import sqlite3, datetime, config
from logger import Logger

Log = Logger().Log

class DB():
    def __init__(self, dbpath=config.db_filename):
        self.dbpath = dbpath
        self.dbcon = self.create_db(self.dbpath)
        self.sql_log = self.__logfileopen()
    def __repr__(self):
        return "SQLITE obj: " + self.dbpath
    def __logfileopen(self):
        if config.sql_log:
            return open(config.sql_log_filename, 'a')
    def db(self):
        return self.dbcon
    def create_db(self, db):
        connection=sqlite3.connect(db)
        cur=connection.cursor()
        try:
            cur.execute('CREATE TABLE x509 (id INTEGER PRIMARY KEY, date TEXT, host TEXT, port INTEGER, cert TEXT, fingerprint TEXT, issuer TEXT, not_after TEXT, not_before TEXT, serial_number TEXT, subject TEXT, version INTEGER, rsa TEXT, modulus TEXT, size INTEGER)')
            cur.execute('CREATE TABLE jobs (id INTEGER PRIMARY KEY, startdate TEXT, enddate TEXT, startip INTEGER, stopip INTEGER, fetched INTEGER)')
            connection.commit()
        except sqlite3.OperationalError:
            ""
            Log("%s: tables seems to be already created." % self.dbpath, 3)
        return connection
    def now(self):
        return str(datetime.datetime.now())
    def execute_query(self, query, values=None):
        if self.sql_log:
            self.sql_log.write(query + '\n')
        cur = self.db().cursor()
        try:
            if values:
                for i in range(len(values)):
                    values[i] = str(values[i]) #I AM ASHAMED OF USING THIS SHIT! WHY DO I NEED TO?
                cur.execute(query, values)
            else:
                cur.execute(query)
        except sqlite3.OperationalError:
            Log('sql execute exception caught:\n%s' % query, 4)
            return
        self.db().commit()
        return cur.fetchall()
    def insert_x509(self, all):
        date = self.now()
        all = [date] + all
        query = 'INSERT INTO x509(date, host, port, cert, fingerprint, issuer, not_after, not_before, serial_number, subject, version, rsa, modulus, size) \
        VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' #is this bad?
        self.execute_query(query, all)
    def insert_job(self, start_ip, stop_ip):
        date = self.now(),
        all = date + (start_ip, stop_ip)
        query = 'INSERT INTO jobs(startdate, startip, stopip) \
        VALUES("%s", "%s", "%s")' % all
        self.execute_query(query)
    def end_job(self, fetched):
        date = self.now()
        query = 'UPDATE jobs SET enddate = "%s", fetched = "%d" WHERE id = (SELECT id FROM jobs ORDER BY id DESC LIMIT 1)' % (date, fetched) #awful!
        self.execute_query(query)        
    def select_cred(self, username, password):
        query = 'SELECT username, password FROM cred WHERE username = "%s" AND password = "%s"' % (username, password)
        return self.execute_query(query)
    def dump_table(self, table):
        "should be used as a debug"
        query = 'SELECT * FROM %s' % table
        print "-- TABLE DUMP (%s):" % table        
        print self.execute_query(query)
